SQL CASE Clause in SQL Queries

SQL Tutorial Home

The SQL CASE clause can be used in SELECT, DELETE, UPDATE and even in the INSERT statement. In SQL, use columns in many different clauses like the WHERE, the SELECT list, the ORDER BY, or the GROUP BY. What about the cases where the data is not coming from the same column?

Here is an example table:

Name height (meters) weight (kilos) age smoke ?
John 1.85 92 62 false
Mary 1.65 58 35 false
Peter 1.78 104 54 true
Sean 1.94 89 34 true
Tim 1.68 97 76 false

You want to create a report with the name of the person and their BMI value (body mass index). Here are the BMI Categories:

BMI Categories

Category BMI (kg/m2)
Underweight BMI ＜ 18.5
Normal Weight 18.5 ≦ BMI ＜ 25
Overweight 25 ≦ BMI ＜ 30
Obese 30 ≦ BMI

This SQL query uses a CASE clause to calculate the BMI index and, based on the BMI index value, shows the BMI Category.

Note the arithmetic expression weight / height ^ 2 calculates the body mass index. In SQL the operator ^ is used to calculate the power of a number.


SELECT name,
  CASE
    WHEN weight / height ^ 2 < 18.5 THEN ‘Underweight’
    WHEN weight / height ^ 2 BETWEEN 18.5 AND 25 THEN ‘Normal Weight’
    WHEN weight / height ^ 2 BETWEEN 25 AND 30 THEN ‘Overweight’
    WHEN weight / height ^ 2 > 30 THEN ‘Obese’
  END
FROM person

We can also use CASE in the ORDER BY clause. Suppose you want to distribute a Covid vaccine to people ordered by a health risk index. You want people listed in this order:

  • BMI greater than 30 (Obese)
  • Smokers
  • BMI between 25 and 30 (Overweight)
  • Everyone else

You can execute this SQL query to obtain the ordered report:


SELECT name
FROM person
ORDER BY CASE
    WHEN weight / height ^ 2 > 30 THEN 1
    WHEN smoker = TRUE THEN 2 
    WHEN weight / height ^ 2 BETWEN 25 AND 30 THEN 3
    ELSE 4
END
The query is ordered by an integer generated by a CASE statement: assigning 1 to the highest risk group, 2 and 3 to the next groups, and 4 for everyone else.

Here is the result:

Name
Peter
Tim
Sean
John
Mary

Closing Words

The SQL CASE clause is very flexible and can be used in many different places of the SQL SELECT. In this Lesson you learned how to use the CASE clause in the SELECT list of columns and in the ORDER BY. It can also be used in GROUP BY, WHERE, and even subqueries.

With this lesson, you are finished with the Basic tutorials. We covered many different topics, like logical and comparison operators, the IS NULL, LIKE and BETWEEN operators among other topics. Keep going, learn SQL and increase your skills!

IN THIS PAGE